package tests;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

import model.Lesson;
import model.Student;
import model.datasource.DatabaseDataSource;
import model.datasource.DatabaseManager;
import model.datasource.SQLStatements;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.mockito.Mock;
import org.mockito.Mockito;

import common.Constants;

public class DatabaseDataSourceTest {

	@Before
	public void setUp() throws Exception
	{
		DatabaseManager.getInstance().initialize();
	}

	@After
	public void tearDown() throws Exception
	{
		DatabaseManager.getInstance().uninitialize();
	}

	@Test
	public void createTablesTest() throws SQLException
	{
		DatabaseDataSource ds = new DatabaseDataSource();
		
		int res = ds.createTables();		
		assertEquals(Constants.OK, res);
		
		String query = "PRAGMA table_info(student)";
		Connection conn = DatabaseManager.getInstance().getConnection();
		ResultSet rs = conn.createStatement().executeQuery(query);
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "student_id");
		assertEquals("1", rs.getString(6)); // column is PK
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "student_name");
		assertEquals("0", rs.getString(6));
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "student_lastname");
		assertEquals("0", rs.getString(6));
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "student_nickname");
		assertEquals("0", rs.getString(6));
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "student_school");
		assertEquals("0", rs.getString(6));
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "student_phonenumber");
		assertEquals("0", rs.getString(6));
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "student_email");
		assertEquals("0", rs.getString(6));
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "student_ageyear");
		assertEquals("0", rs.getString(6));

		rs.close();
		
		query = "PRAGMA table_info(lecture)";
		rs = conn.createStatement().executeQuery(query);
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "lecture_id");
		assertEquals("1", rs.getString(6)); // column is PK
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "lecture_student_id");
		assertEquals("0", rs.getString(6));
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "lecture_title");
		assertEquals("0", rs.getString(6));
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "lecture_content");
		assertEquals("0", rs.getString(6));
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "lecture_income");
		assertEquals("0", rs.getString(6));
		
		assertTrue(rs.next());
		assertEquals(rs.getString(2), "lecture_date");
		assertEquals("0", rs.getString(6));
		
		rs.close();
		conn.close();
	}
	
	@Test
	public void insertNewStudentTest() throws SQLException
	{
		DatabaseDataSource ds = new DatabaseDataSource();
		Student s = ds.addNewStudent("pera", "peric", "perica", "pera@yahoo.com", "MG", "123", 1988);
		
		assertNotNull(s); // ensuring that Student is not null
		
		String query = "SELECT * FROM student WHERE student_id = " + s.getId();
		Connection conn = DatabaseManager.getInstance().getConnection();
		
		ResultSet rs = conn.createStatement().executeQuery(query);
		assertTrue(rs.next());
		
		assertEquals(s.getId(), rs.getInt("student_id"));
		assertEquals("pera", rs.getString("student_name"));
		assertEquals("peric", rs.getString("student_lastname"));
		assertEquals("perica", rs.getString("student_nickname"));
		assertEquals("pera@yahoo.com", rs.getString("student_email"));
		assertEquals("MG", rs.getString("student_school"));
		assertEquals("123", rs.getString("student_phonenumber"));
		assertEquals(1988, rs.getInt("student_ageyear"));
		
		rs.close();
		conn.close();
	}
	
	@Test
	public void requestAllStudentsTest() throws SQLException
	{
		DatabaseDataSource ds = new DatabaseDataSource();
		Collection<Student> studs = ds.requestAllStudents();
		assertNotNull(studs);
		
		String query = "SELECT * FROM student";
		Connection conn = DatabaseManager.getInstance().getConnection();
		ResultSet rs = conn.createStatement().executeQuery(query);
		
		for(Student s : studs )
		{
			assertTrue(rs.next()); // move to the next student
			
			assertEquals(s.getId(), rs.getInt("student_id"));
			assertEquals(s.getName(), rs.getString("student_name"));
			assertEquals(s.getLastName(), rs.getString("student_lastname"));
			assertEquals(s.getNickname(), rs.getString("student_nickname"));
			assertEquals(s.getEmail(), rs.getString("student_email"));
			assertEquals(s.getSchool(), rs.getString("student_school"));
			assertEquals(s.getPhoneNumber(), rs.getString("student_phonenumber"));
			assertEquals(s.getAgeYear(), rs.getInt("student_ageyear"));
		}	
		rs.close();
		conn.close();
	}
	
	@Test
	public void deleteStudentTest() throws SQLException
	{
		Connection conn = DatabaseManager.getInstance().getConnection();
		String test = "test";
		PreparedStatement ps = conn.prepareStatement(SQLStatements.INSERT_NEW_STUDENT);
		ps.setString(1, test);
		ps.setString(2, test);
		ps.setString(3, test);
		ps.setString(4, test);
		ps.setString(5, test);
		ps.setString(6, test);
		ps.setInt(7, 0);
		
		int res = ps.executeUpdate();
		int id = ps.getGeneratedKeys().getInt(1);
		System.out.println("generated id = " + id);
		
		assertTrue(id > 0);
		ps.close();
		
		ps = conn.prepareStatement("SELECT * FROM student WHERE student_id=?");
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		assertTrue(rs.next());
		
		for( int i = 2; i < 7; i++)
		{
			assertEquals(test, rs.getString(i));
		}
		assertEquals(0, rs.getInt(7));
		rs.close();
		ps.close();
		
		DatabaseDataSource ds = new DatabaseDataSource();
		res = ds.deleteStudent(id);
		
		ps = conn.prepareStatement("SELECT * FROM student WHERE student_id=?");
		ps.setInt(1, id);
		rs = ps.executeQuery();
		
		assertFalse(rs.next());
		
		rs.close();
		ps.close();
				
		conn.close();
	}
	
	@Test
	public void updateStudentTest() throws SQLException
	{
		Connection conn = DatabaseManager.getInstance().getConnection();
		String test = "test";
		String testModifed = test + "_modified";
		PreparedStatement ps = conn.prepareStatement(SQLStatements.INSERT_NEW_STUDENT);
		ps.setString(1, test);
		ps.setString(2, test);
		ps.setString(3, test);
		ps.setString(4, test);
		ps.setString(5, test);
		ps.setString(6, test);
		ps.setInt(7, 0);
		
		int res = ps.executeUpdate();
		int id = ps.getGeneratedKeys().getInt(1);
		System.out.println("generated id = " + id);
		
		assertTrue(id > 0);
		ps.close();
		
		ps = conn.prepareStatement("SELECT * FROM student WHERE student_id=?");
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		assertTrue(rs.next());
		
		for( int i = 2; i < 7; i++)
		{
			assertEquals(test, rs.getString(i));
		}
		assertEquals(0, rs.getInt(7));
		rs.close();
		ps.close();
		
		DatabaseDataSource ds = new DatabaseDataSource();
		Student s = ds.updateStudent(id, testModifed, testModifed, testModifed, testModifed, testModifed, testModifed, 10);
		assertNotNull(s);
		assertEquals(id, s.getId());
		
		ps = conn.prepareStatement("SELECT * FROM student WHERE student_id=?");
		ps.setInt(1, s.getId());
		rs = ps.executeQuery();
		assertTrue(rs.next());
		
		for( int i = 2; i < 7; i++)
		{
			assertEquals(testModifed, rs.getString(i));
		}
		System.out.println();
		assertEquals(10, rs.getInt("student_ageyear"));
		rs.close();
		ps.close();
		conn.close();
	}
	
	@Test
	public void insertNewLessonTest() throws SQLException, ParseException
	{
		DatabaseDataSource ds = new DatabaseDataSource();
		Student s = Mockito.mock(Student.class);
		Mockito.when(s.getId()).thenReturn(0);
		Date date = new Date();
		Lesson l = ds.addNewLesson(s, "title", "content", date, 1000);
		
		assertNotNull(l); // ensuring that Student is not null
		
		String query = "SELECT * FROM lecture WHERE lecture_id = " + l.getId();
		Connection conn = DatabaseManager.getInstance().getConnection();
		
		ResultSet rs = conn.createStatement().executeQuery(query);
		assertTrue(rs.next());
		
		assertEquals(l.getId(), rs.getInt("lecture_id"));
		assertEquals("title", rs.getString("lecture_title"));
		assertEquals("content", rs.getString("lecture_content"));
		assertEquals(new SimpleDateFormat(Constants.dateFormat).format(date), rs.getString("lecture_date"));
		assertEquals(1000, rs.getInt("lecture_income"));
		
		rs.close();
		conn.close();
	}
	
	@Test
	public void requestAllLessonsTest() throws SQLException
	{
		DatabaseDataSource ds = new DatabaseDataSource();
		Collection<Lesson> lessons = ds.requestAllLessons();
		assertNotNull(lessons);
		
		String query = "SELECT * FROM lecture";
		Connection conn = DatabaseManager.getInstance().getConnection();
		ResultSet rs = conn.createStatement().executeQuery(query);
		
		for(Lesson l : lessons)
		{
			assertTrue(rs.next()); // move to the next student
			
			assertEquals(l.getId(), rs.getInt("lecture_id"));
			assertEquals(l.getStudent().getId(), rs.getInt("lecture_student_id"));
			assertEquals(l.getTitle(), rs.getString("lecture_title"));
			assertEquals(l.getContent(), rs.getString("lecture_content"));
			assertEquals(new SimpleDateFormat(Constants.dateFormat).format(l.getDate()), rs.getString("lecture_date"));
			assertEquals(l.getIncome(), rs.getInt("lecture_income"));
		}	
		rs.close();
		conn.close();
	}
	
	@Test
	public void deleteLectureTest() throws SQLException, ParseException
	{
		Connection conn = DatabaseManager.getInstance().getConnection();
		String test = "test";
		Date date = new Date();
		PreparedStatement ps = conn.prepareStatement(SQLStatements.INSERT_NEW_LESSON);
		ps.setInt(1, 0);
		ps.setString(2, test);
		ps.setString(3, test);
		ps.setString(4, new SimpleDateFormat(Constants.dateFormat).format(date));
		ps.setInt(5, 1000);
		
		int res = ps.executeUpdate();
		int id = ps.getGeneratedKeys().getInt(1);
		System.out.println("generated id = " + id);
		
		assertTrue(id > 0);
		ps.close();
		
		DatabaseDataSource ds = new DatabaseDataSource();
		res = ds.deleteLesson(id);
		
		ps = conn.prepareStatement("SELECT * FROM lecture WHERE lecture_id=?");
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		
		assertFalse(rs.next());
		
		rs.close();
		ps.close();
				
		conn.close();
	}
	
	@Test
	public void updateLessonTest() throws SQLException
	{
		Connection conn = DatabaseManager.getInstance().getConnection();
		String test = "test";
		String testModified = test + "_modified";
		Date date = new Date();
		PreparedStatement ps = conn.prepareStatement(SQLStatements.INSERT_NEW_LESSON);
		ps.setInt(1, 0);
		ps.setString(2, test);
		ps.setString(3, test);
		ps.setString(4, new SimpleDateFormat(Constants.dateFormat).format(date));
		ps.setInt(5, 1000);
		
		int res = ps.executeUpdate();
		int id = ps.getGeneratedKeys().getInt(1);
		System.out.println("generated id = " + id);
		
		assertTrue(id > 0);
		ps.close();
		Date d = new Date(date.getTime() - 10000000);
		Student mockedStud = Mockito.mock(Student.class);
		Mockito.when(mockedStud.getId()).thenReturn(99);
		DatabaseDataSource ds = new DatabaseDataSource();
		Lesson l = ds.updateLesson(id, mockedStud, testModified, testModified, d, 1000);
		assertNotNull(l);
		assertEquals(id, l.getId());
		
		ps = conn.prepareStatement("SELECT * FROM lecture WHERE lecture_id=?");
		ps.setInt(1, l.getId());
		ResultSet rs = ps.executeQuery();
		assertTrue(rs.next());
		
		assertEquals(id, l.getId());
		
		rs.close();
		ps.close();
		conn.close();

	}
}
